﻿using MyModels.Models;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp
{
    /// <summary>
    /// 经过亲自使用发现 创建数据和创建表功能，不会删除现有的，只会更新，
    /// 如果有新添加的列则添加上，需要特别注意的是删除列功能，如果你新增了一个列，并且添加了数据，
    /// 但是另一个人没有更新代码他也添加了其他列，或者没有添加执行创建数据库和表操作则会把你的列和数据都删除。
    /// </summary>
    internal class SqlSugarClientDemo
    {
        // 1 使用 SqlSugarClient 方式 性能高
        public static void ShowSqlSugar()
        {
            //创建数据库对象
            SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
            {
                // 数据库连接字符串可以使用两种类型 
                // 1 Data Source=127.0.0.1;DataBase=SqlSugarDemo;User ID=root;Password=123456;Charset=utf8;port=3308;SslMode=None;
                // 2 server=localhost;port=3306;uid=root;pwd=123456;database=SqlSugarDemo
                ConnectionString = "server=localhost;port=3306;DataBase=SqlSugarDemo;uid=root;pwd=123456;",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true,
            }, db =>
            {
                db.Aop.OnLogExecuted = (sql, pars) =>
                {
                    Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
                };
            });

            // 创建数据库
            Db.DbMaintenance.CreateDatabase();

            //创建表
            Db.CodeFirst.InitTables<Student>();

            // 查询表的所有数据
            var list = Db.Queryable<Student>().ToList();

            // 插入数据
            Db.Insertable(new Student() { Id = 1, Name = "jack" }).ExecuteCommand();

            // 更新数据
            Db.Updateable(new Student() { Id = 1, Name = "jack2" }).ExecuteCommand();

            // 删除
            Db.Deleteable<Student>().Where(i => i.Id == 1).ExecuteCommand();
        }

        //2 使用 SqlSugarScope 方式
        public static void ShowSqlSugar2()
        {
            // 创建数据库
            SqlSugarHelper.Db.DbMaintenance.CreateDatabase();

            // 创建表
            SqlSugarHelper.Db.CodeFirst.InitTables<TestPK>();

            //// 查询所有数据
            //var list = SqlSugarHelper.Db.Queryable<Student>().ToList();

            //// 插入数据
            //SqlSugarHelper.Db.Insertable(new Student() { Id = 1, Name = "jack" ,Description="这里是描述信息"}).ExecuteCommand();
            //var list2 = SqlSugarHelper.Db.Queryable<Student>().ToList();

            // 更新数据
            //SqlSugarHelper.Db.Updateable(new Student() { Id = 1, Name = "jack2" }).ExecuteCommand();
            //var list3 = SqlSugarHelper.Db.Queryable<Student>().ToList();

            //// 删除
            //SqlSugarHelper.Db.Deleteable<Student>().Where(i => i.Id == 1).ExecuteCommand();
        }

        /// <summary>
        /// 根据自定义特性实现一些自定义功能
        /// </summary>
        public static void ShowSqlSugar3()
        {
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "server=localhost;port=3306;DataBase=SqlSugarDemo;uid=root;pwd=123456;",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true,
                ConfigureExternalServices = new ConfigureExternalServices()
                {
                    EntityService = (property, column) =>
                    {
                        var attributes = property.GetCustomAttributes(true);
                        if (attributes.Any(i => i is KeyAttribute))
                        {
                            // 如果列设置了 [Key] 属性 则这里把他设置为主键
                            column.IsPrimarykey = true;
                        }
                        if (attributes.Any(i => i is StringLengthAttribute))
                        {
                            var length = ((StringLengthAttribute)attributes[0]).MaximumLength;
                            var stringLengthAttr = (attributes.First(i => i is StringLengthAttribute) as StringLengthAttribute);
                            var l = stringLengthAttr.MaximumLength;
                            column.Length = length;
                        }
                    },
                    EntityNameService = (type, entity) =>
                    {
                        var attributes = type.GetCustomAttributes(true);
                        if (attributes.Any(i => i is TableAttribute))
                        {
                            var attr = (attributes.First(i => i is TableAttribute) as TableAttribute);
                            entity.DbTableName = attr.Name;
                        }
                    }
                }
            });

            db.DbMaintenance.CreateDatabase();

            db.CodeFirst.InitTables<Student2>();

        }

        /// <summary>
        /// 根据 SqlSugar 列特性生成数据库相关字段类型
        /// 根据数据库表生成类
        /// </summary>
        public static void ShowSqlSugar4()
        {
            // 创建数据库
            //SqlSugarHelper.Db.DbMaintenance.CreateDatabase();

            // 创建表
            SqlSugarHelper.Db.CodeFirst.SetStringDefaultLength(100).InitTables<CodeFirstTable1>();

            SqlSugarHelper.Db.DbFirst.IsCreateAttribute().StringNullable().CreateClassFile(@"F:\wjaProject\SqlSugarTest\DbFirstClass", "MyModels");
        }

        /// <summary>
        /// 使用事务
        /// </summary>
        public static void ShowSqlSugar5() {
            //创建数据库对象
            SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
            {
                // 数据库连接字符串可以使用两种类型 
                // 1 Data Source=127.0.0.1;DataBase=SqlSugarDemo;User ID=root;Password=123456;Charset=utf8;port=3308;SslMode=None;
                // 2 server=localhost;port=3306;uid=root;pwd=123456;database=SqlSugarDemo
                ConnectionString = "server=localhost;port=3306;DataBase=SqlSugarDemo;uid=root;pwd=123456;",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true,
            }, db =>
            {
                db.Aop.OnLogExecuted = (sql, pars) =>
                {
                    Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
                };
            });

            //// 创建数据库
            //Db.DbMaintenance.CreateDatabase();

            ////创建表
            //Db.CodeFirst.InitTables<Student>();

            //try
            //{
            //    Db.BeginTran();
            //    Console.WriteLine(Db.Queryable<Student>().Count());
            //    Db.Insertable(new Student() { Name = "wja", Description = "这是描述信息" }).ExecuteCommand();

            //    Console.WriteLine(Db.Queryable<Student>().Count());

            //    throw new Exception("出错");
            //    Db.CommitTran();
            //}
            //catch (Exception)
            //{
            //    Db.RollbackTran();
            //    Console.WriteLine(Db.Queryable<Student>().Count());
            //}

            //连接池
            Db.Open();
            Console.WriteLine(Db.Ado.Connection.GetHashCode());
            Db.Close();
            Console.WriteLine(Db.Ado.Connection.GetHashCode());
            Db.Dispose();
            // 下面这一行代码不会报错，因为SqlSugar会重新打开一个连接
            // Close() 方法和 Dispose() 方法的区别是一个使用之前的连接池对象，一个使用新对象。
            Console.WriteLine(Db.Ado.Connection.GetHashCode());
        }

        public static void ShowSqlSugar6() {
            //for (int i = 0; i < 502; i++)
            //{
            //    SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
            //    {
            //        ConnectionString = "server=localhost;port=3306;DataBase=SqlSugarDemo;uid=root;pwd=123456;",
            //        DbType = DbType.MySql,
            //        IsAutoCloseConnection = false,
            //    });
            //    Db.Ado.GetInt("select 1 from dual ");
            //}
            SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "server=localhost;port=3306;DataBase=SqlSugarDemo;uid=root;pwd=123456;",
                DbType = DbType.MySql,
                IsAutoCloseConnection = false,
            }, db =>
            {
                db.Aop.OnLogExecuted = (sql, pars) =>
                {
                    Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
                    // Console.WriteLine(sql);
                };
            });

            //var s1 = new Student { Name = "wja", Description = "" };
            //var s2 = new Student { Name = "张三", Description = "这里是张三的描述" };
            //var s3 = new Student { Name = "李四", Description = "这里是李四的描述" };
            //var s4 = new Student { Name = "张姗姗", Description = "" };
            //var s5 = new Student { Name = "历史书", Description = "这里是李思思的描述" };
            //var list = new List<Student>();
            //list.Add(s1);
            //list.Add(s2);
            //list.Add(s3);
            //list.Add(s4);
            //list.Add(s5);

            //// Db.Insertable(new Student { Name="wja",Description=""}).ExecuteCommand();
            //Db.Insertable(list).ExecuteCommand();

            //List<Student> list = Db.Queryable<Student>().ToList();

            //int count = Db.Queryable<Student>().Count();

            //Db.Queryable<Student>().Where(i => i.Id == 3).ToList();

            // Db.Queryable<Student>().Where(i=>i.Id==2&&i.Name=="zss").ToList();
            //var b=true;
            //var exp = Expressionable.Create<Student>();
            //exp.OrIF(b, i => i.Name == "wja");
            //exp.Or(i => i.Description.Contains("李"));
            //var list = Db.Queryable<Student>().Where(exp.ToExpression()).ToList();

            //var d= Db.Queryable<Student>().InSingle(2);
            //var ds = Db.Queryable<Student>().Single(i => i.Id == 3);
            // 查询第一个
            // var s = Db.Queryable<Student>().First(i => i.Id == 10);

            // 查找最后一个
            // var s = Db.Queryable<Student>().OrderBy(x => x.Id, OrderByType.Desc).First(i => i.Id >2);

            // in 查询 条件是单个字段
            //var ids= new List<int>() { 2,3,4};
            //var s = Db.Queryable<Student>().Where(i => ids.Contains(i.Id)).ToList();

            //// in 查询 多个字段
            //List<Student> list = new List<Student>()
            //{
            //    new Student(){ Name="wja",Description=""},
            //    new Student(){Name="张三",Description="张"}
            //};
            //var ss = Db.Queryable<Student>().Where(i => list.Any(s=>i.Name.Contains(s.Name))).ToList();


        }

        public static void ShowSqlSugar7() {
            // SqlSugarHelper.Db.CodeFirst.InitTables<SysUserInfo>();

            SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "server=localhost;port=3306;DataBase=SqlSugarDemo;uid=root;pwd=123456;",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true,
                ConfigureExternalServices = new ConfigureExternalServices
                {
                    EntityService = (prop, column) =>
                    {
                        // 这里统一把model类中 可空类型 统一创建可空的数据库类型
                        if(column.IsPrimarykey==false && new NullabilityInfoContext().Create(prop).WriteState is NullabilityState.Nullable)
                        {
                            column.IsNullable = true;
                        }
                    },
                    EntityNameService = (x, p) =>
                    {
                        // 驼峰转下划线
                        p.DbTableName = UtilMethods.ToUnderLine(p.DbTableName);
                    }
                }
            }, db =>
            {
                db.Aop.OnLogExecuted = (sql, pars) =>
                {
                    Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
                };
            });
            // 删除表
            // Db.DbMaintenance.DropTable<SysUserInfo>();
          
            // 创建表
            //   Db.CodeFirst.SetStringDefaultLength(100).InitTables<SysUserInfo>();
            // 使用反射 批量创建表
            var path = AppDomain.CurrentDomain.RelativeSearchPath??AppDomain.CurrentDomain.BaseDirectory;
            var referencedAssemblies = Directory.GetFiles(path, "MyModels.dll").Select(Assembly.LoadFrom).ToArray();
            var modelTypes = referencedAssemblies.SelectMany(a=>a.DefinedTypes)
                .Select(type=>type.AsType())
                .Where(x=>x.IsClass && x.Namespace != null && x.Namespace.Equals("MyModels.Models")).ToArray();

            // 创建数据库
            Db.DbMaintenance.CreateDatabase();
            // 先批量删除 然后在批量添加
            // Db.DbMaintenance.DropTable(modelTypes);
            // 然后在批量创建表，这些只适合在项目初期使用，项目开发中后期一般禁止使用的
            Db.CodeFirst.SetStringDefaultLength(50).InitTables(modelTypes);
        }
    }

    public class SqlSugarHelper
    {
        // 注意：这个推荐使用单例模式
        public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig()
        {
            ConnectionString = "server=localhost;port=3306;DataBase=SqlSugarDemo;uid=root;pwd=123456;",
            DbType = DbType.MySql,
            IsAutoCloseConnection = true,
        },
        db =>
        {
            // 全局生效配置点，一般AOP和程序启动的配置放在这里，所以上下文生效
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                Console.WriteLine(sql);
                Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
            };
        });
       
    }

    [SugarTable("dbStudent")] // 设置数据库表名称
    public class Student
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        [SugarColumn(ColumnName = "StudentName")] // 数据库与实体不一样设置列名称
        public string? Name { get; set; }
        public string? Description { get; set; } = null;

        // public int test { get; set; }
    }

    public class TestPK
    {
        [SugarColumn(IsPrimaryKey = true)]
        public Guid Pk1 { get; set; }
        [SugarColumn(IsPrimaryKey = true)]
        public Guid Pk2 { get; set; }

        public string? Name { get; set; }
    }

    [SugarTable("Student")]
    public class Student2
    {
        [Key]
        public int Id { get; set; }
        [StringLength(50)]
        public string? Name { get; set; }
    }

    // 迁移建表使用
    public class CodeFirstTable1
    {
        [SugarColumn(IsIdentity = true,IsPrimaryKey =true)]
        public int Id { get; set; }
        [SugarColumn(IsNullable = true,ColumnDescription ="姓名")]
        public string? Name { get; set; }
        [SugarColumn(IsNullable = true,Length =500,ColumnDescription ="描述")]
        public string? Description { get; set; }
        [SugarColumn(IsNullable =true)]
        public DateTime CreateTime { get; set; }

        public string TestNull { get; set; } = null!;
    }
}
